﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MockPrjTeam1.Entity;
using SD.Commons.Helpers;
using System.Data;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using MockPrjTeam1.Biz;

namespace MockPrjTeam1.DAO
{
    public class ContactDAO
    {
        //public static SqlConnection Conn;
        //public static string connString;
        //public static void Connect()
        //{
        //}
        //public ContactDAO()
        //{
        //    connString = "Data Source=.\sqlexpress;Initial Catalog=ABSD_DB;Integrated Security=True";
        //    Conn = new SqlConnection();
        //    Conn.ConnectionString = connString;
        //    Conn.Open();
        //}
        public List<Contact> GetAll()
        {
            var list = new List<Contact>();

            var results = SqlHelper.ExecuteReader("GetAllContact");

            while (results.Read())
            {
                var item = new Contact();
                MapField(item, results);
                if (item.ManageID != 0)
                {
                    ContactBiz contactBiz = new ContactBiz();
                    Contact contact = contactBiz.GetOne(item.ManageID);
                    if (contact != null)
                    {
                        item.ManagerName = contact.ContactName;

                    }

                }
                list.Add(item);
            }

            return list;
        }
        //public static DataTable GetDataToTable(string sql)
        //{
        //    SqlDataAdapter Mydata = new SqlDataAdapter();
        //    Mydata.SelectCommand = new SqlCommand();
        //    Mydata.SelectCommand.Connection = ContactDAO.Conn;
        //    Mydata.SelectCommand.CommandText = sql;
        //    DataTable table = new DataTable();
        //    Mydata.Fill(table);
        //    return table;
        //}

        public bool Save(Contact contact)
        {
            try
            {

                string sql = "insert into Contact(FirstName,SurName,ContactType,ManageID,KnownAs,OfficePhone,MobilePhone,STHomePhone,EmailAddress,BestContactMethod,JobRole,JobTitle,Workbase,Active,ContactName) values ('" +
                        contact.FirstName + "','" + contact.SurName + "','" + contact.ContactType + "','" +
                    contact.ManageID + "','" + contact.KnownAs + "','" + contact.OfficePhone + "','" + contact.MobilePhone + "','" +
                        contact.STHomePhone + "','" + contact.EmailAddress + "','" + contact.BestContactMethod + "','" +
                        contact.JobRole + "','" + contact.JobTitle + "','" + contact.WorkBase + "','" + contact.Active + "','" + contact.ContactName + "')";
                SqlHelper.RunSql(sql);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        public List<Contact> SearchContact(string FirstName, string SurName)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", FirstName));
            temp.Add(new SqlParameter("p2", SurName));

            var SearchList = new List<Contact>();
            var results = SqlHelper.ExecuteReader("SearchContact", temp);
            while (results.Read())
            {
                var item = new Contact();
                MapField(item, results);
                if (item.ManageID != 0)
                {
                    ContactBiz contactBiz = new ContactBiz();
                    Contact contact = contactBiz.GetOne(item.ManageID);
                    if (contact != null)
                    {
                        item.ManagerName = contact.ContactName;

                    }

                }

                SearchList.Add(item);

            }
            return SearchList;
        }

        public Contact GetOne(int id)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p1", id));
            var item = new Contact();
            var results = SqlHelper.ExecuteReader("GetOneContact", temp);
            if (results.Read())
            {
                MapField(item, results);
                return item;

            }
            return null;

        }
        public bool EditContact(Contact Entity)
        {
            var temp = new Collection<SqlParameter>();
            temp.Add(new SqlParameter("p0", Entity.ContactID));
            temp.Add(new SqlParameter("p1", Entity.FirstName));
            temp.Add(new SqlParameter("p2", Entity.SurName));
            temp.Add(new SqlParameter("p3", Entity.ContactType));
            temp.Add(new SqlParameter("p4", Entity.ManageID));
            temp.Add(new SqlParameter("p5", Entity.KnownAs));
            temp.Add(new SqlParameter("p6", Entity.OfficePhone));
            temp.Add(new SqlParameter("p7", Entity.MobilePhone));
            temp.Add(new SqlParameter("p8", Entity.STHomePhone));
            temp.Add(new SqlParameter("p9", Entity.EmailAddress));
            temp.Add(new SqlParameter("p10", Entity.BestContactMethod));
            temp.Add(new SqlParameter("p11", Entity.JobRole));
            temp.Add(new SqlParameter("p12", Entity.JobTitle));
            temp.Add(new SqlParameter("p13", Entity.WorkBase));
            temp.Add(new SqlParameter("p14", Entity.Active));
            temp.Add(new SqlParameter("p15", Entity.ContactName));

            var result = SqlHelper.ExecuteNonQuery("EditContact", temp);
            if (result != 0)
            {
                return true;
            }
            else return false;

        }
        private void MapField(Contact item, IDataReader results)
        {
            item.ContactID = results.GetInt32(0);
            item.FirstName = results.GetString(1);
            item.SurName = results.GetString(2);
            item.ContactType = results.GetString(3);
            item.ManageID = results.GetInt32(4);
            item.KnownAs = results.GetString(5);
            item.OfficePhone = results.GetString(6);
            item.MobilePhone = results.GetString(7);
            item.STHomePhone = results.GetString(8);
            item.EmailAddress = results.GetString(9);
            item.BestContactMethod = results.GetString(10);
            item.JobRole = results.GetString(11);
            item.JobTitle = results.GetString(12);
            item.WorkBase = results.GetString(13);
            item.Active = results.GetString(14);
            item.ContactName = results.GetString(15);
        }
    }

}